import 'package:ibooks/model/index.dart';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';

class SqlLiteUtil {
  Database database;

  Future initIBookDB() async {
    var databasesPath = await getDatabasesPath();
    String path = join(databasesPath, 'ibook.db');
    print("初始化数据库...");
    //根据数据库文件路径和数据库版本号创建数据库表
    database = await openDatabase(path, version: 1,
        onCreate: (Database db, int version) async {
      await db.execute('''
          CREATE TABLE ${BookMetaData.tbBook} (
            ${BookMetaData.columnId} INTEGER PRIMARY KEY, 
            ${BookMetaData.columnName} TEXT, 
            ${BookMetaData.columnPath} TEXT, 
            ${BookMetaData.columnType} TEXT, 
            ${BookMetaData.columnProcess} INTEGER)
          ''');
    });
    print("database create OK ===> $database");
  }

  // 插入一条书籍数据
  Future<Book> insert(Book book) async {
    var books = await getBookByParam(book);
    if (books == null) {
      book.id = await database.insert(BookMetaData.tbBook, book.toJson());
      print("insert a book data ===> ${book.toJson()}, generate id ===> ${book.id}");
    } else {
      print("book has already exist ===> ${book.toJson()}, id ===> ${books[0].id}");
      return null;
    }
    return book;
  }

  // 查找所有书籍信息
  Future<List<Book>> getAllBooks() async {
    List<Map> maps = await database.query(BookMetaData.tbBook, columns: [
      BookMetaData.columnId,
      BookMetaData.columnName,
      BookMetaData.columnPath,
      BookMetaData.columnPath,
      BookMetaData.columnProcess,
    ]);
    print("select all books data ===> $maps");
    if (maps == null || maps.length == 0) {
      return null;
    }

    List<Book> books = [];
    for (int i = 0; i < maps.length; i++) {
      books.add(Book.fromJson(maps[i]));
    }
    return books;
  }

  Future<List<Book>> getBookByParam(Book book) async {
    List<Map> maps = await database.query(BookMetaData.tbBook,
        columns: [
          BookMetaData.columnId,
          BookMetaData.columnName,
          BookMetaData.columnPath,
          BookMetaData.columnPath,
          BookMetaData.columnProcess,
        ],
      where: '${BookMetaData.columnName} = ? and ${BookMetaData.columnPath} = ?',
      whereArgs: [
        book.name,
        book.path
      ]
    );

    if (maps == null || maps.length == 0) {
      return null;
    }

    List<Book> books = [];
    for (int i = 0; i < maps.length; i++) {
      books.add(Book.fromJson(maps[i]));
    }
    return books;
  }

  Future<int> removeBookById(int id) async {
    print("delete a book, id ===> $id");
    return await database.delete(BookMetaData.tbBook,
        where: '${BookMetaData.columnId} = ?', whereArgs: [id]);
  }

  // 更新书籍信息
  Future<int> update(Book book) async {
    print("update a book, book ===> ${book.toJson()}");
    return await database.update(BookMetaData.tbBook, book.toJson(),
        where: '${BookMetaData.columnId} = ?', whereArgs: [book.id]);
  }

  // 记得及时关闭数据库，防止内存泄漏
  close() async {
    await database.close();
  }

  Future<int> removeAllBooks() async {
    int res = await database.delete(BookMetaData.tbBook,
      where: '${BookMetaData.columnName}  is not null'
    );
    print("drop all books, res ===> $res");
    return res;
  }

  Future<int> removeBooksByIds(List<int> selectIds) async {
//    print();
    String sql = "delete from ${BookMetaData.tbBook} where id in ( ${selectIds.join(", ")} ) ";
    print(sql);
//    int res = await database.delete(BookMetaData.tbBook,
//        where: '${BookMetaData.columnId} in ( ${selectIds.join(", ")} )',
////      whereArgs: [selectIds]
//    );
    int res = await database.rawDelete(sql);
    print("drop selected books, res ===> $res");
    return res;
  }
}

class BookMetaData {
  static final String tbBook = "tb_book";
  static final String columnId = "id";
  static final String columnName = "name";
  static final String columnPath = "path";
  static final String columnType = "type";
  static final String columnProcess = "process";
}
